﻿Imports System.Data.SqlClient

Public Class frmRInfonavit
    Dim tipo As String
    Private Sub frmRInfonavit_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System

        radioAltas.Checked = True
        datBaja1.Value = Now()
        datBaja2.Value = Now()
        tipo = ""
        lstTipo.SelectedItem = "INFONAVIT"
    End Sub
    Sub Vencimiento()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection
        Dim strFecha1 As String = Format(Me.datBaja1.Value.Year, "0000") & Format(Me.datBaja1.Value.Month, "00") & Format(Me.datBaja1.Value.Day, "00")
        Dim strFecha2 As String = Format(Me.datBaja2.Value.Year, "0000") & Format(Me.datBaja2.Value.Month, "00") & Format(Me.datBaja2.Value.Day, "00")

        Dim strSql As String = ""

        If lstTipo.Text = "Infonavit" Then
            tipo = "INFONAVIT"
        Else
            tipo = "FONACOT"
        End If

        strSql = "select E.IDEmpleado,E.Codigo,Nombrecompleto,descripcion,fecha,original,nuevo,motivo,capturo"
        strSql = strSql & " from Empleados E INNER JOIN InfonavitFonacot L"
        strSql = strSql & " ON E.IDEmpleado=L.IDEmpleado INNER JOIN Departamentos D ON e.IdDepartamento=d.IdDepartamento WHERE EstadoEmpleado<>'B' and tipo='" & tipo & "'"
        strSql = strSql & " order by fecha"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader


        wb.SHEETS(1).cells(rng, 1).value = "Clave"
        wb.SHEETS(1).cells(rng, 2).value = "Nombre del Empleado"
        wb.SHEETS(1).cells(rng, 3).value = "Departamento"
        wb.SHEETS(1).cells(rng, 4).value = "Fecha"
        wb.SHEETS(1).cells(rng, 5).value = "Monto Original"
        wb.SHEETS(1).cells(rng, 6).value = "Monto Nuevo"
        wb.SHEETS(1).cells(rng, 7).value = "Capturó"

        Dim i As Integer
        For i = 1 To 7
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).font.size = 9
            wb.SHEETS(1).cells(rng, i).interior.colorindex = 1
            wb.SHEETS(1).cells(rng, i).font.colorindex = 2
        Next


        Dim cont As Integer = 0
        Dim Vigencia As Date
        Dim strFechaMov As String = ""

        rng = rng + 1

        Do While rdBuscar.Read()
            strFechaMov = Format(CDate(rdBuscar("fecha").ToString).Year, "0000") & Format(CDate(rdBuscar("fecha").ToString).Month, "00") & Format(CDate(rdBuscar("fecha").ToString).Day, "00")
            If strFechaMov >= strFecha1 And strFechaMov <= strFecha2 Then
                cont = cont + 1
                wb.SHEETS(1).cells(rng, 1).value = rdBuscar("Codigo").ToString.Trim
                wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Nombrecompleto").ToString.Trim
                wb.SHEETS(1).cells(rng, 3).value = rdBuscar("Descripcion").ToString.Trim
                Vigencia = rdBuscar("fecha")
                wb.SHEETS(1).cells(rng, 4).value = Vigencia.Day & "/" & Vigencia.Month & "/" & Vigencia.Year
                wb.SHEETS(1).cells(rng, 5).value = rdBuscar("original").ToString.Trim
                wb.SHEETS(1).cells(rng, 6).value = rdBuscar("nuevo").ToString.Trim
                wb.SHEETS(1).cells(rng, 7).value = rdBuscar("capturo").ToString.Trim

                For i = 1 To 7
                    wb.SHEETS(1).cells(rng, i).font.size = 9
                Next
                rng = rng + 1
            End If
        Loop

        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 30
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 5
        wb.SHEETS(1).COLUMNS(6).ColumnWidth = 12

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        If tipo = "INFONAVIT" Then
            wb.SHEETS(1).cells(2, 1).value = "Informe de Infonavit. Filtro: Fecha de Vencimiento:  Del " & Me.datBaja1.Value.Day & "/" & Me.datBaja1.Value.Month & "/" & Me.datBaja1.Value.Year & " al " & Me.datBaja2.Value.Day & "/" & Me.datBaja2.Value.Month & "/" & Me.datBaja2.Value.Year
        Else
            wb.SHEETS(1).cells(2, 1).value = "Informe de Fonacot. Filtro: Fecha de Vencimiento:  Del " & Me.datBaja1.Value.Day & "/" & Me.datBaja1.Value.Month & "/" & Me.datBaja1.Value.Year & " al " & Me.datBaja2.Value.Day & "/" & Me.datBaja2.Value.Month & "/" & Me.datBaja2.Value.Year

        End If
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & usuarioactivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
        wb.SHEETS(1).cells(4, 1).value = "Registros del filtro: " & cont
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8
    End Sub
    Sub Todas()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection
        Dim strFecha1 As String = Format(Me.datBaja1.Value.Year, "0000") & Format(Me.datBaja1.Value.Month, "00") & Format(Me.datBaja1.Value.Day, "00")
        Dim strFecha2 As String = Format(Me.datBaja2.Value.Year, "0000") & Format(Me.datBaja2.Value.Month, "00") & Format(Me.datBaja2.Value.Day, "00")

        Dim strSql As String = ""

        If lstTipo.Text = "Infonavit" Then
            tipo = "INFONAVIT"
        Else
            tipo = "FONACOT"
        End If

        strSql = "select E.IDEmpleado,E.Codigo,Nombrecompleto,descripcion,fecha,original,nuevo,motivo,capturo"
        strSql = strSql & " from Empleados E INNER JOIN InfonavitFonacot L"
        strSql = strSql & " ON E.IDEmpleado=L.IDEmpleado INNER JOIN Departamentos D ON e.IdDepartamento=d.IdDepartamento WHERE EstadoEmpleado<>'B' and tipo='" & tipo & "'"
        strSql = strSql & " order by fecha"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader




        wb.SHEETS(1).cells(rng, 1).value = "Clave"
        wb.SHEETS(1).cells(rng, 2).value = "Nombre del Empleado"
        wb.SHEETS(1).cells(rng, 3).value = "Departamento"
        wb.SHEETS(1).cells(rng, 4).value = "Fecha"
        wb.SHEETS(1).cells(rng, 5).value = "Monto Original"
        wb.SHEETS(1).cells(rng, 6).value = "Monto Nuevo"
        wb.SHEETS(1).cells(rng, 7).value = "Capturó"

        Dim i As Integer
        For i = 1 To 7
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).font.size = 9
            wb.SHEETS(1).cells(rng, i).interior.colorindex = 1
            wb.SHEETS(1).cells(rng, i).font.colorindex = 2
        Next

        Dim cont As Integer = 0
        Dim Vigencia As Date
        Dim strFechaMov As String = ""

        rng = rng + 1

        Do While rdBuscar.Read()
            strFechaMov = Format(CDate(rdBuscar("fecha").ToString).Year, "0000") & Format(CDate(rdBuscar("fecha").ToString).Month, "00") & Format(CDate(rdBuscar("fecha").ToString).Day, "00")
            cont = cont + 1
            wb.SHEETS(1).cells(rng, 1).value = rdBuscar("Codigo").ToString.Trim
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Nombrecompleto").ToString.Trim
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("Descripcion").ToString.Trim
            Vigencia = rdBuscar("fecha")
            wb.SHEETS(1).cells(rng, 4).value = Vigencia.Day & "/" & Vigencia.Month & "/" & Vigencia.Year
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("original").ToString.Trim
            wb.SHEETS(1).cells(rng, 6).value = rdBuscar("nuevo").ToString.Trim
            wb.SHEETS(1).cells(rng, 7).value = rdBuscar("capturo").ToString.Trim

            For i = 1 To 7
                wb.SHEETS(1).cells(rng, i).font.size = 9
            Next
            rng = rng + 1

        Loop

        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 30
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(6).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(7).ColumnWidth = 12

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        If tipo = "INFONAVIT" Then
            wb.SHEETS(1).cells(2, 1).value = "Informe de Infonavit. Filtro: Todas"
        Else
            wb.SHEETS(1).cells(2, 1).value = "Informe de Fonacot. Filtro: Todas"
        End If

        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & usuarioactivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
        wb.SHEETS(1).cells(4, 1).value = "Registros del filtro: " & cont
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8
    End Sub

    Private Sub radioAltas_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radioAltas.CheckedChanged
        If radioAltas.Checked = True Then
            datBaja1.Enabled = False
            datBaja2.Enabled = False
        Else
            datBaja1.Enabled = True
            datBaja2.Enabled = True
        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub RadButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub

    Private Sub btnAbrir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub ToolStripButton2_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton2.Click
        If lstTipo.Text = "" Then
            MsgBox("Debes indicar el adeudo a imprimir", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End If
        If radioAltas.Checked = True Then
            Todas()
        Else
            Vencimiento()
        End If
    End Sub

    Private Sub ToolStripButton1_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton1.Click
        Close()

    End Sub
End Class